using System;
using System.Data;
using System.Data.OleDb;

using Geekees.App.Cashbook.Lib.Entity;
using Geekees.App.Cashbook.Lib.Exceptions;
using Geekees.App.Cashbook.Lib.Helper;

namespace Geekees.App.Cashbook.Lib.DAL
{
	/// <summary>
	/// Summary description for DAClassification.
	/// </summary>
	public class DAClassification
	{
		public DAClassification()
		{
		}

		public static DataTable GetAllClassification( int userId )
		{
			string sql = "SELECT * FROM [Classification] WHERE [UserId]=@UserId";
			OleDbCommand cmd = new OleDbCommand( sql );
			cmd.Parameters.Add( "@UserId", OleDbType.Integer ).Value = userId;
			SqlHelper h = new SqlHelper();
			try
			{
				h.Open();
				DataTable dt = h.ExecuteDataTable( cmd );

				return dt;
			}
			catch( Exception ex )
			{ 
				string s = ex.Message;
				throw; 
			}
			finally{ h.Close(); }

		}

		public static int AddClassification( string cName, int userId )
		{
			int i = Consts.NO_ID;
			string sql = "INSERT INTO [Classification] ( Name, SortIndex, UserId ) VALUES " + 
				"( @Name,  @SortIndex, @UserId ) ";
			string sqlId= " SELECT MAX(Id) AS ThisID FROM [Classification]";
			
			OleDbCommand cmd = new OleDbCommand( sql );
			cmd.Parameters.Add( "@Name", OleDbType.VarChar ).Value = cName;
			cmd.Parameters.Add( "@SortIndex", OleDbType.Integer ).Value = 0;
			cmd.Parameters.Add( "@UserId", OleDbType.Integer ).Value = userId;
			
			OleDbCommand cmdId = new OleDbCommand( sqlId );
			
			SqlHelper h = new SqlHelper();
			try
			{
				h.Open();
				h.ExecuteScalar( cmd );
				i = (int)h.ExecuteScalar( cmdId );
			}
			catch( Exception ex )
			{ 
				string s = ex.Message;
				throw; 
			}
			finally{ h.Close(); }
			
			return i;
		}

		public static bool UpdateClassification( int cId, string cName, int userId )
		{
			bool result = false;
			string sql = "UPDATE [Classification] SET [Name]=@Name WHERE [Id]=@Id AND [UserId]=@UserId";
			
			OleDbCommand cmd = new OleDbCommand( sql );
			cmd.Parameters.Add( "@Name", OleDbType.VarChar ).Value = cName;
			cmd.Parameters.Add( "@Id", OleDbType.Integer ).Value = cId;
			cmd.Parameters.Add( "@UserId", OleDbType.Integer ).Value = userId;
			
			SqlHelper h = new SqlHelper();
			try
			{
				h.Open();
				result = h.ExecuteNonQuery( cmd ) > 0;
			}
			catch( Exception ex )
			{ 
				string s = ex.Message;
				throw; 
			}
			finally{ h.Close(); }
			
			return result;
		}

		public static bool DeleteClassification( int cId, int userId )
		{
			bool result = false;

			//if there are categories in the classification, user cannot delete the classification.
			DataTable dt = DACategory.GetCategoryByClassificationId( userId, cId );
			if( dt != null && dt.Rows.Count > 0 )
			{
				throw new DeleteException("You cannot delete this classification because there are categories in this classification.");
			}

			string sql = "DELETE FROM [Classification] WHERE [Id]=@Id AND [UserId]=@UserId";
			
			OleDbCommand cmd = new OleDbCommand( sql );
			cmd.Parameters.Add( "@Id", OleDbType.Integer ).Value = cId;
			cmd.Parameters.Add( "@UserId", OleDbType.Integer ).Value = userId;
			
			SqlHelper h = new SqlHelper();
			try
			{
				h.Open();
				result = h.ExecuteNonQuery( cmd ) > 0;
			}
			catch( Exception ex )
			{ 
				string s = ex.Message;
				throw; 
			}
			finally{ h.Close(); }
			
			//DACategory.DeleteCategoryByClassification( cId, userId );

			return result;		
		}
	}
}
